package com.collect.collectclient.xml.mapper;

import cn.hutool.core.lang.UUID;
import com.collect.collectclient.xml.dto.response.RoadTrafficStatus;
import com.collect.collectclient.xml.dto.response.TrafficAlarm;
import com.collect.collectclient.xml.dto.response.TrafficEvent;
import com.collect.collectclient.xml.dto.response.TrafficManagement;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;

@Repository
public class XmlRepository {
    @Resource
    private JdbcTemplate jdbcTemplate;

    public RoadTrafficStatus getRoadTrafficList(){
        String sql="select b.*,\n" +
                "case \n" +
                "when b.speed=0 then '无数据'\n" +
                "when b.speed>0 and b.speed<20 then '拥堵'\n" +
                "when b.speed >=20 and b.speed< 35 then '缓慢'\n" +
                "when b.speed >=35 then '畅通'\n" +
                "end as 'roadStatus',NOW() as 'DetecTime'\n" +
                "  from (\n" +
                "select a.*,w.way_name as 'direction',c.cross_name as 'LocationName',ss.area_name,c.area_id from (\n" +
                "select s.cross_way_id,\n" +
                "(avg(s.speed)) as 'speed'\n" +
                " from tra_entitydetector s where 1=1 \n" +
                "GROUP BY s.cross_way_id\n" +
                ") a  left join tra_cross_way w on w.id=a.cross_way_id\n" +
                "left join tra_cross c on c.id=w.cross_id\n" +
                "left join tra_area ss on ss.id=c.area_id\n" +
                " ) b";
        List<RoadTrafficStatus> status=jdbcTemplate.query(sql,(rs, down)->{
            RoadTrafficStatus traffic=RoadTrafficStatus.builder().build();
            traffic.setSpeed(rs.getString("speed"));
            traffic.setRoadStatus(rs.getString("roadStatus"));
            traffic.setDirection(rs.getString("direction"));
            traffic.setLocationName(rs.getString("area_name"));
            traffic.setFileUrl("http://xxx");
            traffic.setLocationID(rs.getObject("area_id").toString());
            traffic.setDetectTime(rs.getString("DetecTime"));
            traffic.setProgramID(UUID.randomUUID().toString().replace("-",""));
            return traffic;
        });
        return status.get(0);
    }

    public TrafficManagement getManagementList(){
        String sql="select s.job_desc,s.job_name\n" +
                ",s.plan_start_time,s.plan_end_time,c.config_name from tra_job s left join tra_job_config c on c.id=s.config_road_id order by s.create_time desc limit 1";
        return jdbcTemplate.queryForObject(sql,(rs, rowNum) -> {
            TrafficManagement student = new TrafficManagement();
            student.setContent(rs.getString("job_desc"));
            student.setReason(rs.getString("job_name"));
            student.setManagementType(rs.getString("config_name"));
            student.setFileUrl("http://xxxxx");
            student.setProgramID(UUID.randomUUID().toString().replace("-",""));
            student.setEndTime(rs.getString("plan_end_time"));
            student.setStartTime(rs.getString("plan_start_time"));
            return student;
        });
    }

    public TrafficEvent getEventList(){
        String sql="select s.incident_location_name as 'location',a.area_name,\n" +
                "s.incident_desc as 'content',m.monitor_latitude as 'latitude'\n" +
                ",m.monitor_longitude as 'longitude' ,i.item_text as  'alarmType',s.create_time as 'occurTime'\n" +
                "from tra_detector_incident s \n" +
                "left join tra_monitor m on m.id=s.detector_id\n" +
                "left join sys_dict_item i on i.item_value=s.incident_type and i.type_Code='incident_type'\n" +
                "left join tra_cross c on c.id=m.location_id\n" +
                "left join tra_area a on a.id=c.area_id\n" +
                "where m.monitor_latitude is not  null \n" +
                "and i.item_text!='交通事故' and s.incident_desc is not null\n" +
                "order by s.create_time desc limit 1";
        return jdbcTemplate.queryForObject(sql,(rs, rowNum) -> {
            TrafficEvent student = new TrafficEvent();
            student.setContent(rs.getString("content"));
            student.setAffect(rs.getString("area_name"));
            student.setEventType(rs.getString("alarmType"));
            student.setFileUrl("http://xxxxx");
            student.setProgramID(UUID.randomUUID().toString().replace("-",""));
            student.setLatitude(rs.getString("latitude"));
            student.setLongitude(rs.getString("longitude"));
            student.setLocation(rs.getString("location"));
            student.setOccurTime(rs.getString("occurTime"));
            return student;
        });
    }

    public TrafficAlarm getAlarmList(){
        String sql="select s.incident_location_name as 'location',a.area_name,\n" +
                "s.incident_desc as 'content',m.monitor_latitude as 'latitude'\n" +
                ",m.monitor_longitude as 'longitude' ,i.item_text as  'alarmType',s.create_time as 'occurTime'\n" +
                "from tra_detector_incident s \n" +
                "left join tra_monitor m on m.id=s.detector_id\n" +
                "left join sys_dict_item i on i.item_value=s.incident_type and i.type_Code='incident_type'\n" +
                "left join tra_cross c on c.id=m.location_id\n" +
                "left join tra_area a on a.id=c.area_id\n" +
                "where m.monitor_latitude is not  null \n" +
                "and i.item_text='交通事故' and s.incident_desc is not null\n" +
                "order by s.create_time desc limit 1";
        return jdbcTemplate.queryForObject(sql,(rs, rowNum) -> {
            TrafficAlarm student = new TrafficAlarm();
            student.setContent(rs.getString("content"));
            student.setAffect(rs.getString("area_name"));
//            student.setEventType(rs.getString("alarmType"));
            student.setFileUrl("http://xxxxx");
            student.setProgramID(UUID.randomUUID().toString().replace("-",""));
            student.setLatitude(rs.getString("latitude"));
            student.setLongitude(rs.getString("longitude"));
            student.setLocation(rs.getString("location"));
            student.setOccurTime(rs.getString("occurTime"));
            return student;
        });
    }
}
